package ${daoImpPack};

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import ${daoPack}.${className}Dao;
import ${entityPack}.${className};

   /**
    * @ClassName ${className}DaoJdbc实现类
    * @date ${.now?date} 
    * @author ${author}
    */  
@Repository
public class ${className}DaoJdbc implements ${className}Dao {
	private static Logger log = Logger.getLogger(${className}DaoJdbc.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public int insert${className}(${className} ${instName}) {
		String sqlStr = "insert into ${tableName} ( "
						<#list columns! as value> 
							+" ${value.name} ,"	
						</#list>
							+"is_del) values( "
						<#list columns! as value> 
							 +" ?,"
						</#list>	 
							 +" 0 )";
		log.debug(sqlStr+"\n"+${instName}.toString());
		try{
			Object[] args = {
			<#list columns! as value>
				<#if value_index < columns?size-1>
					${instName}.get${value.jname?cap_first}(),
				<#else>
					${instName}.get${value.jname?cap_first}()
				</#if>
			</#list>
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据添加异常 SQL："+sqlStr+"\n"+${instName}.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int update${className}(${className} ${instName}) {
		String sqlStr = "update ${tableName} set "
						<#list columns! as value>	
							<#if value_index < columns?size-1>
									+" ${value.name} = ? , "
							<#else>
									+" ${value.name} = ?  "
							</#if>		
						</#list>			
									+" where id = ?";
		log.debug(sqlStr+"\n"+${instName}.toString());
		try{
			Object[] args = {
				<#list columns! as value>
					${instName}.get${value.jname?cap_first}(),
				</#list>
					${instName}.getId()
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据更新异常 SQL："+sqlStr+"\n"+${instName}.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int delete${className}(int id) {
		String sqlStr = "update ${tableName} set is_del = 1  where id = ?";
		try{
			Object[] args = {
					id
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据删除异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return 0;
	}

	@Override
	public ${className} select${className}(int id) {
		String sqlStr = "select * from ${tableName} where is_del =0 and id = ?";
		log.debug(sqlStr+"\nID="+id);
		try{
			Object[] args = {
				id
			};
			return jdbcTemplate.queryForObject(sqlStr, args, new ${className}Mapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return null;
	}

	@Override
	public ${className} select${className}(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select * from ${tableName} where is_del =0 " );
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+"=? ");
				args[i] = queryValues.get(i);
			}
		}
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args, new ${className}Mapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCount${className}() {
		String sqlStr = "select count(1) from ${tableName} where is_del =0 ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.queryForObject(sqlStr, Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<${className}> selectLimit${className}(int start, int size) {
		String sqlStr = "select ";
			<#list columns! as value> 
				<#if value_index < columns?size-1> 
					sqlStr+=" ${value.name} ,";
				<#else>
					sqlStr+=" ${value.name} ";
				</#if>		
			</#list>
			sqlStr+=" from ${tableName} where is_del =0  order by id desc limit ?,?";
		log.debug(sqlStr);
		try{
		Object[] args = {
				start,
				size
		};
			return jdbcTemplate.query(sqlStr, args, new ${className}Mapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<${className}> selectAll${className}() {
		String sqlStr = "select ";
		<#list columns! as value> 
				<#if value_index < columns?size-1> 
					sqlStr+=" ${value.name} ,";
				<#else>
					sqlStr+=" ${value.name} ";
				</#if>		
			</#list>
		 sqlStr+=" from ${tableName} where is_del =0 order by id desc ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.query(sqlStr, new ${className}Mapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCount${className}(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select count(1) from ${tableName} where is_del = 0 ");
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
				args[i] = queryValues.get(i) ;
			}
		}		
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args,Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<${className}> selectLimit${className}(List<String> queryKeys,List<Object> queryValues,int start,int size) {
		StringBuffer sqlStr = new StringBuffer("select ");
			<#list columns! as value> 
				<#if value_index < columns?size-1> 
					sqlStr.append(" ${value.name} ,");
				<#else>
					sqlStr.append(" ${value.name} ");
				</#if>		
			</#list>		
			sqlStr.append(" from ${tableName} where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()+2];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
					args[i] =  queryValues.get(i) ;
				}
			}
		if(args==null){
			args = new Object[]{start,size};
		}else{
			args[queryKeys.size()] = start;
			args[queryKeys.size()+1] = size;
		}
		sqlStr.append(" order by id desc ");
		sqlStr.append(" limit ?,?");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new ${className}Mapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<${className}> selectAll${className}(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select ");
			<#list columns! as value> 
				<#if value_index < columns?size-1> 
					sqlStr.append(" ${value.name} ,");
				<#else>
					sqlStr.append(" ${value.name} ");
				</#if>		
			</#list>
			sqlStr.append("from ${tableName} where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" = ? ");
					args[i] = queryValues.get(i);
				}
			}
		sqlStr.append(" order by id desc ");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new ${className}Mapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	protected class ${className}Mapper implements RowMapper<${className}> {
		@Override
		public ${className} mapRow(ResultSet arg0, int arg1) throws SQLException {
			${className} ${instName} = new ${className}();
			if(arg0!=null){
				<#list columns! as value>
					${instName}.set${value.jname?cap_first}(arg0.get${value.jtype?cap_first}("${value.name}"));
				</#list>
				}
			return ${instName};
		}  
	}
}

